<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Manual_invoice_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}
	public function get_stat() {
		$this->db->where('lookup_type', "UBS_INVOICE_STATUS");
		$query = $this->db->get('app_lookup_values');
		return $query->result();
	}
	public function get_rec_acct() {
		$sql = "SELECT * FROM gl_accounts WHERE sub_type = 'REC' AND enable_flag = 'Y' ";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_rec_acct_add() {
		$sql = "SELECT * FROM gl_accounts WHERE sub_type = 'REC' AND enable_flag = 'Y' AND acct_code != '0002' ";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_cus_account() {
		$sql = "SELECT * FROM ar_account WHERE enable_flag = 'Y' ";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_list_options($id) {
		//$sql = "SELECT * FROM ubs_chg_list_line ucll INNER JOIN ubs_chg_elements uce ON ucll.charge_code = uce.charge_code WHERE list_id = '" . $id . "' ";
		$sql = "SELECT * FROM inv_items";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_types(){
		$sql = "SELECT * FROM app_lookup_values where lookup_type = 'UBS_INVOICE_TYPE' AND lookup_code != 'BILL' ";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function get_account_data($id) {
		$sql = "SELECT alv.meaning meaning, aa.acct_type acct_type , aa.acct_name entity_name , aed.address_1 address, aa.account_id , aac.list_name list_name, aac.list_id list_id, aed.address_id address_id, aa.acct_num acct_num
            FROM ar_account aa
                INNER JOIN ar_entity_address aed ON aed.address_id = aa.bill_to_addr
                INNER JOIN (SELECT arc.account_id account_id, ucl.list_name list_name, ucl.list_id list_id FROM ar_account_chargelist arc , ubs_chg_list ucl WHERE arc.list_id = ucl.list_id) aac ON aac.account_id = aa.account_id
                INNER JOIN app_lookup_values alv ON alv.lookup_code = aa.acct_type
                WHERE aa.account_id = '" . $id . "'
                ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function get_invoice_next() {
		$this->db->query("call get_next_invoice_num(@inv_num)");
		$que = $this->db->query("SELECT @inv_num as invoice_num ");
		return $que->result();
	}
	public function get_uom_data() {
		$query = $this->db->select('*')
			->from('inv_uom')
			->where('enable_flag', "Y")
			->get();
		return $query->result();
	}

	public function get_invoice_payment_status($id){
		return $this->db->query("SELECT ui.invoice_id,
								 SUM(uil.line_amt) line_amt, 
								 (SELECT SUM(`pay_amount`) pay_amt FROM ar_payments WHERE invoice_id = '".$id."') pay_amt
								FROM ubs_invoices ui 
								LEFT JOIN ubs_invoice_lines uil ON uil.`invoice_id` = ui.invoice_id
								WHERE ui.invoice_id = '".$id."'")->result();
	}

	public function get_invoice_data($id) {
		$sql = "SELECT *, ui.description inv_desc, ui.status inv_stat  FROM ubs_invoices ui
                INNER JOIN ar_account aa ON aa.account_id = ui.customer_id
                INNER JOIN app_lookup_values alv ON alv.lookup_code = ui.account_type AND alv.`lookup_type` = 'ACCOUNT_TYPE'
                INNER JOIN ar_entity_address aea ON aea.address_id = ui.address_id
                INNER JOIN ubs_chg_list ucl ON ucl.list_id = ui.charge_list_id
        WHERE ui.invoice_id = '" . $id . "' ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function update_invoice($data) {
		$this->db->query("call ubs_invoices_upd(?,?,?,?,?,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg");
		echo json_encode($que->result());
	}
	public function insert_invoice($data) {
		$this->db->query("call ubs_invoices_ins(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@id,@err,@msg,?)", $data);
		$que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
		echo json_encode($que->result());
	}
	public function insert_invoice_inline($data) {
		$this->db->query("call ubs_invoice_lines_ins_2(?,?,?,?,?,?,?,?,?,?,?,@id,@err,@msg)", $data);
		$que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
		return $que->result();
	}

	public function update_invoice_totalBAL($data, $inv_id) {
		$this->db->where('invoice_id', $inv_id);
		$this->db->update('ubs_invoices', $data);
	}

	public function get_inv_line($id) {
		$sql = "SELECT uil.line_num line_num,ii.item_id, ii.item_num, uil.description, ii.unit_selling_price, uil.usage_qty usage_qty, iu.uom_name uom_name, uil.rate rate,  uil.tax_amt tax_amt, uil.line_amt line_amt
              FROM ubs_invoice_lines uil
                  LEFT JOIN inv_items ii ON ii.item_id = uil.item_id
                  LEFT JOIN inv_uom iu ON iu.uom_code = uil.uom_code
              WHERE uil.invoice_id = '" . $id . "' ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function countArr($arr, $query) {
		$counter = 0;
		foreach ($arr as $key) {
			$counter++;
		}
		$arr[$counter] = $query;
		return $arr;
	}
	public function search_invoice($inv_no, $acct_no, $name,$inv_date) {
		$sql;
		$arg1 = "";
		$arg2 = "";
		$arg3 = "";
		$arg4 = "";
		$arr = array();

		$setter = 0;

		if ($inv_no != "") {
			$arg1 = "ui.invoice_num LIKE '" . $inv_no . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg1);
		}
		if ($acct_no != "") {
			$arg2 = "ui.account_num LIKE '" . $acct_no . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg2);
		}
		if ($name != "") {
			$arg3 = "aa.acct_name LIKE '" . $name . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg3);
		}
		if ($inv_date != "") {
			$arg4 = "ui.invoice_date LIKE '" . $inv_date . "'";
			$setter = 1;
			$arr = $this->countArr($arr, $arg4);
		}
		if ($setter == 1) {
			$que = implode(" AND ", $arr);
			$sql = "SELECT ui.invoice_id invoice_id, ui.account_num account_num , alv.meaning meaning,  aa.acct_name entity_name, aea.address_1 address, ui.invoice_num invoice_num, ui.invoice_date invoice_date, ui.due_date due_date, ui.invoice_total invoice_total
                  FROM ubs_invoices ui
                    INNER JOIN app_lookup_values alv ON alv.lookup_code = ui.account_type AND alv.`lookup_type` = 'ACCOUNT_TYPE'
                    INNER JOIN ar_account aa ON aa.account_id = ui.customer_id
                    INNER JOIN ar_entity_address aea ON aea.address_id = ui.address_id
                  WHERE ui.invoice_type = 'INV' AND " . $que . ";
                  ";
		} else {
			$sql = "SELECT ui.invoice_id invoice_id, ui.account_num account_num , alv.meaning meaning,  aa.acct_name entity_name, aea.address_1 address, ui.invoice_num invoice_num, ui.invoice_date invoice_date, ui.due_date due_date, ui.invoice_total invoice_total
                  FROM ubs_invoices ui
                  INNER JOIN app_lookup_values alv ON alv.lookup_code = ui.account_type AND alv.`lookup_type` = 'ACCOUNT_TYPE'
                  INNER JOIN ar_account aa ON aa.account_id = ui.customer_id
                  INNER JOIN ar_entity_address aea ON aea.address_id = ui.address_id
                  WHERE ui.invoice_type = 'INV' AND ui.invoice_num LIKE '" . $inv_no . "' ";
		}
		$query = $this->db->query($sql);
		if ($query->num_rows() != 0) {
			return $query->result();
		} else {
			$result = "No results found.";
			return $result;
		}
	}
}